>=3B ps aux|grep psq=
l
2255 =3B0.0 =3B0.0 155636 =3B1668 pts/1 =3B=
=3BS =3B =3BJul05 =3B 0:00 psql
3883 =
=3B0.0 =3B0.0 155636 =3B1676 pts/1 =3B =3BS =3B &n=
bsp=3BJul05 =3B 0:00 psql
4672 =3B0.0 =3B0.0 15563=
6 =3B1672 pts/1 =3B =3BS =3B =3BJul05 =3B 0:0=
0 psql
4713 =3B0.0 =3B0.0 155636 =3B1672 pts/1 &nb=
sp=3B =3BS =3B =3BJul05 =3B 0:00 psql
4737 &n=
bsp=3B0.0 =3B0.0 155636 =3B1672 pts/1 =3B =3BS =3B=
=3BJul05 =3B 0:00 psql
4798 =3B0.0 =3B0.0 15=
5636 =3B1668 pts/1 =3B =3BS =3B =3BJul05 =3B =
0:00 psql
5050 =3B0.0 =3B0.0 155636 =3B1676 pts/1 =
=3B =3BS =3B =3BJul05 =3B 0:00 psql
5086=
=3B0.0 =3B0.0 155636 =3B1668 pts/1 =3B =3BS =
=3B =3BJul05 =3B 0:00 psql
5405 =3B0.0 =3B0.0=
155636 =3B1668 pts/1 =3B =3BS =3B =3BJul05 =
=3B 0:00 psql
7255 =3B0.0 =3B0.0 155644 =3B1796 pt=
s/1 =3B =3BS =3B =3BJul05 =3B 0:00 psql
>
psql -c 'select * from =3B"DAPP".s=
tudent_common_data where student_id =3D 1000 and field_id =3D1988=3B' =
=3Bdoes make the ps aux more informative but it does not capture the query =
timing. From what I understand you cannot mix =3B('timing + query') in=
"-c" mode.
So trying to set 'timing on' outside t=
he individual queries (and preferably outside the client machines) somewher=
e on the server so that psql -c on client would capture the timing automati=
cally.
>=3B From: bruce@m=
omjian.us
>=3B Subject: Re: [ADMIN] Change to 'timing on' globally
=
>=3B To: b_ki@hotmail.com
>=3B Date: Tue=2C 6 Jul 2010 10:48:48 -040=
0
>=3B CC: alvherre@commandprompt.com=3B pgsql-admin@postgresql.org
>>=3B
>=3B Balkrishna Sharma wrote:
>=3B >=3B
>=3B >=
=3B Thanks. If I want to do at system-wide level=2C where do I store the
>>=3B >=3B psqlrc file (assuming I want to change the timing behavior s=
ystem-wide)?
>=3B
>=3B >=3B (CentOS 5=2C Postgres 8.4)
>=
=3B >=3B $ ./pg_config --sysconfdir/opt/PostgreSQL/8.4/etc/postgresql
=
>=3B
>=3B >=3B But I don't have /opt/PostgreSQL/8.4/etc/postgresq=
l directory. Just
>=3B >=3B creating the directory and putting a psq=
lrc file over there does not
>=3B >=3B seem to work.
>=3B
&=
gt=3B I just tested it here on Ubuntu and it worked:
>=3B
>=3B =
$ sudo mkdir etc
>=3B $ sudo mkdir etc/postgresql
>=3B $ cd etc=
/postgresql/
>=3B $ sudo vi psqlrc
>=3B # add \echo test
>=
=3B $ pwd
>=3B /opt/PostgreSQL/8.4/etc/postgresql
>=3B $ ../..=
/bin/psql -U postgres postgres
>=3B -->=3B test
>=3B psql (8.4=
..2)
>=3B Type "help" for help.
>=3B
>=3B postgres=3D#
r>>=3B
>=3B >=3B On a side-note=2C I observered that timing value=
in ~/.psqlrc was
>=3B >=3B ignored by psql -c "..." command but not=
by echo "...."|psqlThought
>=3B >=3B it was strange.
>=3B
=
>=3B Yeah=2C that is odd.
>=3B
>=3B --
>=3B Bruce Momj=
ian <=3Bbruce@momjian.us>=3B http://momjian.us
>=3B Ente=
rpriseDB http://enterprisedb.com
>=3B
=
>=3B + None of us is going to be here forever. +
<=
br />
Hotmail is redefining busy with tools for the New Busy. Get more=
from your inbox.
?ocid=3DPID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2' target=3D'_new'>Se=
e how.
=
--_b8345835-e341-4b04-83a3-b28dc25b1ffe_--
Re: Change to "timing on" globally
am 06.07.2010 17:29:12 von Bruce Momjian
Balkrishna Sharma wrote:
>
> > I just tested it here on Ubuntu and it worked:I followed your steps and it worked in the way you indicated, on CentOS as well. But it still does not:a. work with psql -c "query" syntax. (Works in echo mode or in interactive mode.)b. it does not still seem to work if you fire the queries from a client box (in any mode - interactive or otherwise)ON SERVER I get:Timing is on. now------------------------------ 2010-07-06 11:06:13.16734-04(1 row)Time: 0.574 ms
>
> ON CLIENT I just get: now------------------------------- 2010-07-06 11:06:28.455395-04(1 row)
>
> ~~~~~~~~~~~~~~~~~~~~Basically I am firing a lot of psql through unix script on several client machines and a lot of the psql are hanging for some other reasons. I also need to capture the timing of each query. So I need timing to be on.
> Doing the following captures the timing but I don't know which psql statement is hanging when I do ps aux|grep psqlecho '\timing \\select * from ........' | psqlOn ps aux|grep psql I just see:> ps aux|grep psql2255 0.0 0.0 155636 1668 pts/1 S Jul05 0:00 psql3883 0.0 0.0 155636 1676 pts/1 S Jul05 0:00 psql4672 0.0 0.0 155636 1672 pts/1 S Jul05 0:00 psql4713 0.0 0.0 155636 1672 pts/1 S Jul05 0:00 psql4737 0.0 0.0 155636 1672 pts/1 S Jul05 0:00 psql4798 0.0 0.0 155636 1668 pts/1 S Jul05 0:00 psql5050 0.0 0.0 155636 1676 pts/1 S Jul05 0:00 psql5086 0.0 0.0 155636 1668 pts/1 S Jul05 0:00 psql5405 0.0 0.0 155636 1668 pts/1 S Jul05 0:00 psql7255 0.0 0.0 155644 1796 pts/1 S Jul05 0:00 p
sql
>
> psql -c 'select * from "DAPP".student_common_data where student_id = 1000 and field_id =1988;' does make the ps aux more informative but it does not capture the query timing. From what I understand you cannot mix ('timing + query') in "-c" mode.
> So trying to set 'timing on' outside the individual queries (and preferably outside the client machines) somewhere on the server so that psql -c on client would capture the timing automatically.
I think you need to look at postgresql.conf variables like
log_min_duration_statement, and you are right that psqlrc is only going
to be read for clients on the server machine, and only via psql.
------------------------------------------------------------ ---------------
>
>
>
> > From: bruce@momjian.us
> > Subject: Re: [ADMIN] Change to 'timing on' globally
> > To: b_ki@hotmail.com
> > Date: Tue, 6 Jul 2010 10:48:48 -0400
> > CC: alvherre@commandprompt.com; pgsql-admin@postgresql.org
> >
> > Balkrishna Sharma wrote:
> > >
> > > Thanks. If I want to do at system-wide level, where do I store the
> > > psqlrc file (assuming I want to change the timing behavior system-wide)?
> >
> > > (CentOS 5, Postgres 8.4)
> > > $ ./pg_config --sysconfdir/opt/PostgreSQL/8.4/etc/postgresql
> >
> > > But I don't have /opt/PostgreSQL/8.4/etc/postgresql directory. Just
> > > creating the directory and putting a psqlrc file over there does not
> > > seem to work.
> >
> > I just tested it here on Ubuntu and it worked:
> >
> > $ sudo mkdir etc
> > $ sudo mkdir etc/postgresql
> > $ cd etc/postgresql/
> > $ sudo vi psqlrc
> > # add \echo test
> > $ pwd
> > /opt/PostgreSQL/8.4/etc/postgresql
> > $ ../../bin/psql -U postgres postgres
> > --> test
> > psql (8.4.2)
> > Type "help" for help.
> >
> > postgres=#
> >
> > > On a side-note, I observered that timing value in ~/.psqlrc was
> > > ignored by psql -c "..." command but not by echo "...."|psqlThought
> > > it was strange.
> >
> > Yeah, that is odd.
> >
> > --
> > Bruce Momjian http://momjian.us
> > EnterpriseDB http://enterprisedb.com
> >
> > + None of us is going to be here forever. +
>
> ____________________________________________________________ _____
> Hotmail is redefining busy with tools for the New Busy. Get more from your inbox.
> http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326 ::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin